﻿using System;
using System.Collections.Generic;
using System.Text;
using Csla;
using System.Data.Odbc;
using MinaspetroLibrary.Configuracoes;
using System.Data;
using Csla.Data;

namespace MinaspetroLibrary.CurriculoPack
{
    [Serializable()]
    public class Curriculo : BusinessBase<Curriculo>
    {
        #region Control Properties
        [Csla.NotUndoable()]
        private bool _isEmpty = false;

        public bool IsEmpty
        {
            get { return _isEmpty; }
        }
        #endregion

        #region Business Properties and Methods

        //declare members

        private Guid _id = Guid.NewGuid();

        private String _nome = String.Empty;

        private String _senha = String.Empty;
        
        private DateTime _dataNascimento = DateTime.Now;

        private Guid _idEndereco = Guid.Empty;

        private Guid _idContato = Guid.Empty;

        private Guid _idAreaInteresse = Guid.Empty;
        
        private String _objetivos = String.Empty;
        
        private String _qualificacoes = String.Empty;
        
        private String _observacoes = String.Empty;
        
        private DateTime _dataCadastro = DateTime.Now;

        [System.ComponentModel.DataObjectField(true, true)]
        public Guid Id
        {
            get
            {
                CanReadProperty("Id", true);
                return _id;
            }
        }

        public String Nome
        {
            get
            {
                CanReadProperty("Nome", true);
                return _nome;
            }
            set
            {
                CanWriteProperty("Nome", true);
                if (value == null) value = String.Empty;
                if (!_nome.Equals(value))
                {
                    _nome = value;
                    PropertyHasChanged("Nome");
                }
            }
        }

        public String Senha
        {
            get
            {
                CanReadProperty("Senha", true);
                return _senha;
            }
            set
            {
                CanWriteProperty("Senha", true);
                if (value == null) value = String.Empty;
                if (!_senha.Equals(value))
                {
                    _senha = value;
                    PropertyHasChanged("Senha");
                }
            }
        }

        public DateTime DataNascimento
        {
            get
            {
                CanReadProperty("DataNascimento", true);
                return _dataNascimento;
            }
            set
            {
                CanWriteProperty("DataNascimento", true);
                if (value == null) value = DateTime.Now;
                if (!_dataNascimento.Equals(value))
                {
                    _dataNascimento = value;
                    PropertyHasChanged("DataNascimento");
                }
            }
        }

        public Guid IdEndereco
        {
            get
            {
                CanReadProperty("IdEndereco", true);
                return _idEndereco;
            }
            set
            {
                CanWriteProperty("IdEndereco", true);
                if (value == null) value = Guid.NewGuid();
                if (!_idEndereco.Equals(value))
                {
                    _idEndereco = value;
                    PropertyHasChanged("IdEndereco");
                }
            }
        }

        public Guid IdAreaInteresse
        {
            get
            {
                CanReadProperty("IdAreaInteresse", true);
                return _idAreaInteresse;
            }
            set
            {
                CanWriteProperty("IdAreaInteresse", true);
                if (value == null) value = Guid.NewGuid();
                if (!_idAreaInteresse.Equals(value))
                {
                    _idAreaInteresse = value;
                    PropertyHasChanged("IdAreaInteresse");
                }
            }
        }



        public Guid IdContato
        {
            get
            {
                CanReadProperty("IdContato", true);
                return _idContato;
            }
            set
            {
                CanWriteProperty("IdContato", true);
                if (value == null) value = Guid.NewGuid();
                if (!_idContato.Equals(value))
                {
                    _idContato = value;
                    PropertyHasChanged("IdContato");
                }
            }
        }

        public String Objetivos
        {
            get
            {
                CanReadProperty("Objetivos", true);
                return _objetivos;
            }
            set
            {
                CanWriteProperty("Objetivos", true);
                if (value == null) value = String.Empty;
                if (!_objetivos.Equals(value))
                {
                    _objetivos = value;
                    PropertyHasChanged("Objetivos");
                }
            }
        }

        public String Qualificacoes
        {
            get
            {
                CanReadProperty("Qualificacoes", true);
                return _qualificacoes;
            }
            set
            {
                CanWriteProperty("Qualificacoes", true);
                if (value == null) value = String.Empty;
                if (!_qualificacoes.Equals(value))
                {
                    _qualificacoes = value;
                    PropertyHasChanged("Qualificacoes");
                }
            }
        }

        public String Observacoes
        {
            get
            {
                CanReadProperty("Observacoes", true);
                return _observacoes;
            }
            set
            {
                CanWriteProperty("Observacoes", true);
                if (value == null) value = String.Empty;
                if (!_observacoes.Equals(value))
                {
                    _observacoes = value;
                    PropertyHasChanged("Observacoes");
                }
            }
        }

        public DateTime DataCadastro
        {
            get
            {
                CanReadProperty("DataCadastro", true);
                return _dataCadastro;
            }
            set
            {
                CanWriteProperty("DataCadastro", true);
                if (value == null) value = DateTime.Now;
                if (!_dataCadastro.Equals(value))
                {
                    _dataCadastro = value;
                    PropertyHasChanged("DataCadastro");
                }
            }
        }


        protected override object GetIdValue()
        {
            return _id.ToString();
        }

        #endregion //Business Properties and Methods

        #region Validation Rules
        private void AddCustomRules()
        {
            //add custom/non-generated rules here...
        }

        private void AddCommonRules()
        {
        }

        protected override void AddBusinessRules()
        {
            AddCommonRules();
            AddCustomRules();
        }
        #endregion //Validation Rules

        #region Authorization Rules
        protected override void AddAuthorizationRules()
        {
            //TODO: Define authorization rules in Curriculo
        }


        public static bool CanGetObject()
        {
            //TODO: Define CanGetObject permission in Curriculo
            return true;
            //if (Csla.ApplicationContext.User.IsInRole("CurriculoViewGroup"))
            //	return true;
            //return false;
        }

        public static bool CanAddObject()
        {
            //TODO: Define CanAddObject permission in Curriculo
            return true;
            //if (Csla.ApplicationContext.User.IsInRole("CurriculoAddGroup"))
            //	return true;
            //return false;
        }

        public static bool CanEditObject()
        {
            //TODO: Define CanEditObject permission in Curriculo
            return true;
            //if (Csla.ApplicationContext.User.IsInRole("CurriculoEditGroup"))
            //	return true;
            //return false;
        }

        public static bool CanDeleteObject()
        {
            //TODO: Define CanDeleteObject permission in Curriculo
            return true;
            //if (Csla.ApplicationContext.User.IsInRole("CurriculoDeleteGroup"))
            //	return true;
            //return false;
        }
        #endregion //Authorization Rules

        #region Factory Methods
        private Curriculo()
        { /* require use of factory method */ }

        public static Curriculo NewCurriculo()
        {
            if (!CanAddObject())
                throw new System.Security.SecurityException("Usuário não autorizado a adicionar Curriculo");
            return DataPortal.Create<Curriculo>();
        }

        public static Curriculo GetCurriculo(Guid id)
        {
            if (!CanGetObject())
                throw new System.Security.SecurityException("Usuário não autorizado a visualizar Curriculo");
            return DataPortal.Fetch<Curriculo>(new Criteria(id));
        }

        public override Curriculo Save()
        {
            if (IsDeleted && !CanDeleteObject())
                throw new System.Security.SecurityException("Usuário não autorizado a remover Curriculo");
            else if (IsNew && !CanAddObject())
                throw new System.Security.SecurityException("Usuário não autorizado a adicionar Curriculo");
            else if (!CanEditObject())
                throw new System.Security.SecurityException("Usuário não autorizado a atualizar Curriculo");

            return base.Save();
        }

        public static void DeleteCurriculo(Guid id)
        {
            if (!CanDeleteObject())
                throw new System.Security.SecurityException("Usuário não autorizado a remover Curriculo");
            DataPortal.Delete(new Criteria(id));
        }

        #endregion //Factory Methods

        #region Data Access

        #region Criteria

        [Serializable()]
        private class Criteria
        {
            public Guid Id;

            public Criteria(Guid Id)
            {
                this.Id = Id;

            }
        }

        #endregion //Criteria

        #region Data Access - Create
        [RunLocal]
        protected override void DataPortal_Create()
        {
            _isEmpty = false;
            ValidationRules.CheckRules();
        }

        #endregion //Data Access - Create

        #region Data Access - Fetch
        private void DataPortal_Fetch(Criteria criteria)
        {
            using (OdbcConnection cn = new OdbcConnection(ConfigDatabase.MinaspetroConnection))
            {
                cn.Open();

                ExecuteFetch(cn, criteria);
            }//using

        }

        private void ExecuteFetch(OdbcConnection cn, Criteria criteria)
        {
            using (OdbcCommand cm = cn.CreateCommand())
            {
                cm.CommandType = CommandType.Text;
                cm.CommandText = GetFetchScript(criteria);

                using (SafeDataReader dr = new SafeDataReader(cm.ExecuteReader()))
                {
                    FetchObject(dr);
                    ValidationRules.CheckRules();

                    //load child object(s)
                    FetchChildren(dr);
                }
            }//using
        }

        private String GetFetchScript(Criteria criteria)
        {
            return "SELECT Id, Nome, Senha, DataNascimento, IdEndereco, IdContato, IdAreaInteresse, Objetivos, Qualificacoes, Observacoes, DataCadastro "
                 + "FROM curriculotb WHERE Id=\"" + criteria.Id.ToString() + "\";";
        }

        private void FetchObject(SafeDataReader dr)
        {
            _isEmpty = !dr.Read();
            if (_isEmpty) return;

            _id = new Guid(dr["Id"].ToString());
            _nome = dr.GetString("Nome"); 
            _senha = dr.GetString("Senha");
            _dataNascimento = dr.GetDateTime("DataNascimento");
            _idEndereco = new Guid(dr["IdEndereco"].ToString());
            _idContato = new Guid(dr["IdContato"].ToString());
            if (dr["IdAreaInteresse"] != null)
                _idAreaInteresse = new Guid(dr["IdAreaInteresse"].ToString());
            _objetivos = dr.GetString("Objetivos");
            _qualificacoes = dr.GetString("Qualificacoes");
            _observacoes = dr.GetString("Observacoes");
            _dataCadastro = dr.GetDateTime("DataCadastro");

        }

        private void FetchChildren(SafeDataReader dr)
        {
        }
        #endregion //Data Access - Fetch

        #region Data Access - Insert
        protected override void DataPortal_Insert()
        {
            using (OdbcConnection cn = new OdbcConnection(ConfigDatabase.MinaspetroConnection))
            {
                cn.Open();

                ExecuteInsert(cn);

                //update child object(s)
                UpdateChildren(cn);
            }//using
            MinaspetroLibrary.UsuarioPack.Usuario.UsuarioInSession.AtualizarHitorico("Insert - curriculotb - Id: " + _id.ToString());
        }

        private void ExecuteInsert(OdbcConnection cn)
        {
            using (OdbcCommand cm = cn.CreateCommand())
            {
                cm.CommandType = CommandType.Text;
                cm.CommandText = GetInsertScript();

                cm.ExecuteNonQuery();



            }//using
        }

        private String GetInsertScript()
        {

            StringBuilder query = new StringBuilder();
            
            if (!_idAreaInteresse.ToString().Equals("00000000-0000-0000-0000-000000000000"))
            {
                query.Append("INSERT INTO curriculotb(Id, Nome, Senha, DataNascimento, IdEndereco, IdContato, IdAreaInteresse, Objetivos, Qualificacoes, Observacoes, DataCadastro) VALUES (\"");
            }else
            {
                query.Append("INSERT INTO curriculotb(Id, Nome, Senha, DataNascimento, IdEndereco, IdContato, Objetivos, Qualificacoes, Observacoes, DataCadastro) VALUES (\"");
            }
            
            query.Append( _id.ToString() + "\", \"");
            query.Append( _nome.ToString() + "\", \"");
            query.Append( _senha.ToString() + "\", \"");
            query.Append( _dataNascimento.ToString("yyyy-MM-dd HH:mm:ss") + "\", \"");
            query.Append( _idEndereco.ToString() + "\", \"");
            query.Append( _idContato.ToString() + "\", \"");
            if (!_idAreaInteresse.ToString().Equals("00000000-0000-0000-0000-000000000000"))
                query.Append(_idAreaInteresse.ToString() + "\", \"");
            query.Append( _objetivos.ToString() + "\", \"");
            query.Append( _qualificacoes.ToString() + "\", \"");
            query.Append( _observacoes.ToString() + "\", \"");
            query.Append(_dataCadastro.ToString("yyyy-MM-dd HH:mm:ss") + "\")");

            

            return query.ToString();
        }
        #endregion //Data Access - Insert

        #region Data Access - Update
        protected override void DataPortal_Update()
        {
            using (OdbcConnection cn = new OdbcConnection(ConfigDatabase.MinaspetroConnection))
            {
                cn.Open();

                if (base.IsDirty)
                {
                    ExecuteUpdate(cn);
                }

                //update child object(s)
                UpdateChildren(cn);
            }//using
            MinaspetroLibrary.UsuarioPack.Usuario.UsuarioInSession.AtualizarHitorico("Update - curriculotb - Id: " + _id.ToString());
        }

        private void ExecuteUpdate(OdbcConnection cn)
        {
            using (OdbcCommand cm = cn.CreateCommand())
            {
                cm.CommandType = CommandType.Text;
                cm.CommandText = GetUpdateScript();

                if (cm.ExecuteNonQuery() == 0)
                    throw new Exception("Este registro foi editado por outro usuario.");
            }//using
        }

        private String GetUpdateScript()
        {
            StringBuilder query = new StringBuilder();
            
             query.Append("Update curriculotb set ");
                    query.Append( "Nome=\"" + _nome.ToString() + "\", ");
                    query.Append( "Senha=\"" + _senha.ToString() + "\", ");
                    query.Append( "DataNascimento=\"" + _dataNascimento.ToString("yyyy-MM-dd HH:mm:ss") + "\", ");
                    query.Append( "IdEndereco=\"" + _idEndereco.ToString() + "\", ");
                    query.Append( "IdContato=\"" + _idContato.ToString() + "\", ");
                    if (!_idAreaInteresse.ToString().Equals("00000000-0000-0000-0000-000000000000"))
                        query.Append( "IdAreaInteresse=\"" + _idAreaInteresse.ToString() + "\", ");
                    query.Append( "Objetivos=\"" + _objetivos.ToString() + "\", ");
                    query.Append( "Qualificacoes=\"" + _qualificacoes.ToString() + "\", ");
                    query.Append( "Observacoes=\"" + _observacoes.ToString() + "\", ");
                    query.Append( "DataCadastro=\"" + _dataCadastro.ToString("yyyy-MM-dd HH:mm:ss") + "\" ");
                    query.Append("WHERE Id=\"" + _id.ToString() + "\";");

            return query.ToString();
        }

        private void UpdateChildren(OdbcConnection cn)
        {
        }
        #endregion //Data Access - Update

        #region Data Access - Delete
        protected override void DataPortal_DeleteSelf()
        {
            DataPortal_Delete(new Criteria(_id));
        }

        private void DataPortal_Delete(Criteria criteria)
        {
            using (OdbcConnection cn = new OdbcConnection(ConfigDatabase.MinaspetroConnection))
            {
                cn.Open();

                ExecuteDelete(cn, criteria);

            }//using
            MinaspetroLibrary.UsuarioPack.Usuario.UsuarioInSession.AtualizarHitorico("Delete - curriculotb - Id: " + _id.ToString());
        }

        private void ExecuteDelete(OdbcConnection cn, Criteria criteria)
        {
            using (OdbcCommand cm = cn.CreateCommand())
            {
                cm.CommandType = CommandType.Text;
                cm.CommandText = cm.CommandText = "DELETE from curriculotb WHERE Id=\"" + criteria.Id.ToString() + "\";";

                cm.ExecuteNonQuery();
            }//using
        }
        #endregion //Data Access - Delete




        #region Validações
        public static Guid ValidarLoginSenha(string email, string senha)
        {
            using (OdbcConnection cn = new OdbcConnection(ConfigDatabase.MinaspetroConnection))
            {
                cn.Open();

                using (OdbcCommand cm = cn.CreateCommand())
                {
                    cm.CommandType = CommandType.StoredProcedure;
                    cm.CommandText = "SELECT cl.Id FROM curriculotb cl INNER JOIN contatotb co ON cl.IdContato = co.Id WHERE co.Email=\"" + email + "\" and cl.Senha=\"" + senha + "\" ;";

                    using (SafeDataReader dr = new SafeDataReader(cm.ExecuteReader()))
                    {
                        if (dr.Read())
                            return new Guid(dr["Id"].ToString());
                    }
                }
            }
            return Guid.Empty;
        }

        public static bool ExisteLogin(string email)
        {
            using (OdbcConnection cn = new OdbcConnection(ConfigDatabase.MinaspetroConnection))
            {
                cn.Open();

                using (OdbcCommand cm = cn.CreateCommand())
                {
                    cm.CommandType = CommandType.Text;
                    cm.CommandText = "FROM curriculotb cl INNER JOIN contatotb co ON cl.IdContato = co.Id WHERE co.Email=\"" + email + "\"  ";

                    using (SafeDataReader dr = new SafeDataReader(cm.ExecuteReader()))
                    {
                        return dr.Read();
                    }
                }
            }
        }

        public static bool ExisteLogin(string email, Guid idExceto)
        {
            using (OdbcConnection cn = new OdbcConnection(ConfigDatabase.MinaspetroConnection))
            {
                cn.Open();

                using (OdbcCommand cm = cn.CreateCommand())
                {
                    cm.CommandType = CommandType.Text;
                    cm.CommandText = "FROM curriculotb cl INNER JOIN contatotb co ON cl.IdContato = co.Id WHERE co.Email=\"" + email + "\" "
                                    + " and Id != '" + idExceto.ToString() + "' ";

                    using (SafeDataReader dr = new SafeDataReader(cm.ExecuteReader()))
                    {
                        return dr.Read();
                    }
                }
            }
        }
        #endregion Validações




        #endregion //Data Access
    }
}
